HEAD <<<<<<< HEAD ======= >>>>>>> master ======= >>>>>>> master
DCPO Halawa and Data Analyst Loftus asked if it would be possible to use Cook County Medical Examiner Office data, in addition to internal sources, to measure shooting incidents involving Department invovled youth. While that question was answered in another document, the full capability of these analytic tools could not be demonstrated due to security concerns regarding department data. This report is an attempt to more completely showcase the possibilities of GIS powered dashboards and reports.
The requirements for this report have not changed significantly as the following steps are still required:
Importing the medical examiner’s (ME) office data is simple: The Cook County Open Data website has a link that when incorporated into code of the analysis, allows for easy analysis. This data is updated daily, and because of the link, no file needs to downloaded then uploaded to the analytic code. In short, powering on the report loads the most current data into the analysis.
Even though this data is coming from directly from the ME office’s database, cleaning will still need to be done. The data is already fairly tidy, but they are organized by the rules establisehd by the ME, and contains variables that are not – as of this writing – of interest to the department:
| casenumber | incident_date | death_date | age | gender | race | latino | manner | primarycause | gunrelated | opioids | incident_street | longitude | latitude | objectid | location.type | location.coordinates | cold_related | heat_related | residence_zip | incident_zip | secondarycause | primarycause_lineb | primarycause_linec | residence_city | incident_city |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| ME2014-00627 | 1411311600 | 1411325400 | 25 | Male | Black | FALSE | UNDETERMINED | DROWNING | FALSE | FALSE | 499 E. North Water St. | -87.61544627360976 | 41.889496395313714 | 48783 | Point | c(-87.6154462736098, 41.8894963953137) | NO | NO | 60608 | NA | NA | NA | NA | NA | NA |
| ME2014-00649 | 1411422000 | 1411422900 | 61 | Male | Black | FALSE | NATURAL | HYPERTENSIVE ARTERIOSCLEROTIC CARDIOVASCULAR DISEASE | FALSE | FALSE | 1317 PORTLAND | -87.6232147232388 | 41.50732804103343 | 48784 | Point | c(-87.6232147232388, 41.5073280410334) | NO | NO | 60411 | 60411 | NA | NA | NA | NA | NA |
| ME2014-00712 | 1411855080 | 1411855800 | 51 | Male | White | FALSE | NATURAL | DIABETIC KETOACIDOSIS | FALSE | FALSE | 8759 PELIKIN | -87.84975118706316 | 41.77358560899294 | 48785 | Point | c(-87.8497511870632, 41.7735856089929) | NO | NO | 60525 | 60525 | NA | NA | NA | NA | NA |
| ME2014-00792 | 1412226000 | 1412277300 | 55 | Male | Black | FALSE | NATURAL | HYPERTENSIVE AND ARTERIOSCLEROTIC CARDIOVASCULAR DISEASE | FALSE | FALSE | 7750 South Emerald | -87.64324736907044 | 41.75285594967308 | 48786 | Point | c(-87.6432473690704, 41.7528559496731) | NO | NO | NA | NA | NA | NA | NA | NA | NA |
The steps for this data set, then, include removing those variables that are of no interest and filtering results to focus on the age-range of interest to the department. Additionally, the way the ME classify’s race is complicated. Race is listed as Black, White, Other, Asian, NA, Unknown, Am. Indian. Latino is listed as a seperate factor. For the department’s needs, the ME’s data needs to add Latino to race. This is a simple cleaning function in R, that creates the follow categories: Black, White, Other, Asian, NA, Unknown, White Latino, Latino, Am. Indian, Black Latino, NA Latino, Unknown Latino, Am. Indian Latino, Asian Latino
The cleaned ME dataset has following 27 columns: casenumber, incident_date, death_date, age, gender, race, latino, manner, primarycause, gunrelated, opioids, incident_street, longitude, latitude, objectid, location.type, location.coordinates, cold_related, heat_related, residence_zip, incident_zip, secondarycause, primarycause_lineb, primarycause_linec, residence_city, incident_city, source. Displaying all of this data in a single column is not useful on paper, but because this report is web based, we can add scroll bars to the table in order to view all the entries.
| casenumber | incident_date | death_date | age | gender | race | latino | manner | primarycause | gunrelated | opioids | incident_street | longitude | latitude | objectid | location.type | location.coordinates | cold_related | heat_related | residence_zip | incident_zip | secondarycause | primarycause_lineb | primarycause_linec | residence_city | incident_city | source |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| ME2017-02502 | 1496301060 | 1496356080 | 19 | Male | Black | FALSE | HOMICIDE | GUNSHOT WOUND OF HEAD | TRUE | FALSE | intersection of andover and revere | NA | NA | 49395 | NA | NULL | NO | NO | 60411 | 60411 | NA | NA | NA | Sauk Village | CHICAGO HEIGHTS | Medical Examiner |
| ME2017-00331 | 1484773200 | 1484867640 | 19 | Male | Black | FALSE | HOMICIDE | GUNSHOT WOUND OF TORSO | TRUE | FALSE | 4200 GEORGIA STREET | NA | NA | 49479 | NA | NULL | NO | NO | 46410 | 46409 | NA | NA | NA | Merrillville | GARY | Medical Examiner |
| ME2017-02072 | 1494136800 | 1494170100 | 21 | Male | White | FALSE | SUICIDE | GUNSHOT WOUND OF HEAD | TRUE | FALSE | 1860 S ARLINGTON HTS RD | -87.98399339672822 | 42.05188610113801 | 49481 | Point | c(-87.9839933967282, 42.051886101138) | NO | NO | 60067 | 60005 | NA | NA | NA | Palatine | ARLINGTON HEIGHTS | Medical Examiner |
| ME2017-02406 | 1495901700 | 1495903620 | 16 | Male | Black | FALSE | HOMICIDE | GUNSHOT WOUNDS OF THE CHEST | TRUE | FALSE | 11 N. Madion Street | -87.77516605946164 | 41.87996258959737 | 49585 | Point | c(-87.7751660594616, 41.8799625895974) | NO | NO | 60612 | 60302 | NA | NA | NA | Chicago | OAK PARK | Medical Examiner |
| ME2017-00581 | 1486167600 | 1486169280 | 26 | Male | White Latino | TRUE | HOMICIDE | GUNSHOT WOUND OF CHEST | TRUE | FALSE | MAPLE EAST OF JACKSON | NA | NA | 49589 | NA | NULL | NO | NO | 60139 | 60303 | NA | NA | NA | Glendale Heights | OAK PARK | Medical Examiner |
| ME2017-05649 | 1512277140 | 1512278940 | 23 | Male | Black | FALSE | HOMICIDE | MULTIPLE GUNSHOT WOUNDS | TRUE | FALSE | 7201 W. NORTH AVE | -87.80634190943427 | 41.90857041261212 | 49590 | Point | c(-87.8063419094343, 41.9085704126121) | NO | NO | NA | 60305 | NA | NA | NA | NA | RIVER FOREST | Medical Examiner |
| ME2017-00242 | 1484365440 | 1484404200 | 20 | Male | Black | FALSE | HOMICIDE | GUNSHOT WOUND OF HEAD | TRUE | FALSE | S Orchard Rd And Cornell Ave | NA | NA | 49678 | NA | NULL | NO | NO | 60506 | 60538 | NA | NA | NA | Aurora | MONTGOMERY | Medical Examiner |
| ME2017-00795 | 1486608120 | 1487179200 | 25 | Male | Black | FALSE | HOMICIDE | GUNSHOT WOUND OF BACK | TRUE | FALSE | 345 S Canal St | -87.63962853261914 | 41.888229470064914 | 49692 | Point | c(-87.6396285326191, 41.8882294700649) | NO | NO | 55443 | 60606 | NA | NA | NA | Minneapolis | CHICAGO | Medical Examiner |
| ME2017-03878 | 1503212280 | 1503213660 | 22 | Male | Black | FALSE | HOMICIDE | MULTIPLE GUNSHOT WOUNDS | TRUE | FALSE | 1042 WEST MAXWELL | -87.6526164063243 | 41.864741147421995 | 49753 | Point | c(-87.6526164063243, 41.864741147422) | NO | NO | 60608 | NA | NA | NA | NA | Chicago | CHICAGO | Medical Examiner |
| ME2017-00986 | 1488093540 | 1488097620 | 18 | Male | White Latino | TRUE | HOMICIDE | GUNSHOT WOUND OF BACK | TRUE | FALSE | 10942 S MACKINAW | -87.54307994556501 | 41.696109911920374 | 49789 | Point | c(-87.543079945565, 41.6961099119204) | NO | NO | 60617 | 60617 | NA | NA | NA | Chicago | CHICAGO | Medical Examiner |
A more useful way of displaying the data is summarizing by categories:
| manner | race | gender | Total |
|---|---|---|---|
| HOMICIDE | Black | Female | 26 |
| HOMICIDE | Black | Male | 303 |
| HOMICIDE | Black Latino | Male | 2 |
| HOMICIDE | Latino | Male | 2 |
| HOMICIDE | Other | Male | 3 |
| HOMICIDE | White | Female | 1 |
| HOMICIDE | White | Male | 6 |
| HOMICIDE | White Latino | Female | 5 |
| HOMICIDE | White Latino | Male | 65 |
| SUICIDE | Black | Male | 13 |
| SUICIDE | White | Female | 4 |
| SUICIDE | White | Male | 11 |
| SUICIDE | White Latino | Female | 1 |
| SUICIDE | White Latino | Male | 4 |
| UNDETERMINED | Black | Male | 2 |
And by plotting the data.
<<<<<<< HEAD <<<<<<< HEADIn short, the data can be filtered, selected and summarized for descriptive statistics, or displayed as a variety of graphs (bar, line, bubble, etc.) With additional time and resources, this data can be combined with the department’s internal data for more analysis and insight. Furthermore, additional time and web formating will allow for interactive graphs that would allow individual users to analyze the data in a variety of ways.
=======In short, the data can be selected for visualization, aggregated for totals, or displayed as a bar graph. With additional time and resources, this data can be combined with the department’s internal data for more analysis and insight.
>>>>>>> masterBecause this report is a website, the static GIS maps are interactive. For the purposes of this report,the visualizations are relatively static – It is possible to zoom in on particular neighborhoods and have pop-ups on points – but versions of these maps could be made with user-based selection and filter criteria.
Cook County Shooting Incidents 2016 - Today. Source Cook County Medical Examiner’s Office
## Warning in pal(v): Some values were outside the color scale and will be
## treated as NA
=======
>>>>>>> master
=======
>>>>>>> master
A note about the data sets: The ME data was initially downloaded and cleaned before mapped. Their records were missing 16 location entries. In a more formal report, these missing records would be more thoroughly examined. For this proof of concept, they were simply removed from the data set.
These maps are not limited to yearly intervals: Chloropleths can be based on daily, monthly, or quarterly dates. The restrictions of this level of detail is not the amount of work, but where the data will be displayed. This report was written as a webpage and printed to PDF because that level of work is relatively easy. If this report was a standard report, then in addition to being a web page, it would also be printable as a PDF. However, another option would be the creation of a dashboard.
Using the data and methods contained in this document, creating a GIS-informed report that runs regularly would be a relatively simple task. Creating a dashboard or app to display the same data would only be marginally more intensive. The most pressing concern about a dashboard/app would be getting permission from County stakeholders to approve the use of an internal web server.
The advantages of a dashboard/app would be the ability to filter the data sets and display them in real time, instead of “hard coding” results like this report. This method would increase collaboration, free up staff time for other analysis, generate less paper, and allow for easy access for all OCJ staff. A note about CFive. They may, or may not, have this capacity built in a future build. This report is not currently contained within the Statement of Work and CFive uses different methods for visualizations. Additionally, to date we have yet to see any of their forms or reports.
A dashboard of the ME’s data can be prototyped within a few weeks. Allyson’s data cannot be displayed this way unless additional steps are taken to protect the identity of court-involved youth.
The data provided by Allyson can be plotted onto a map of the city or county to reveal counts of shooting incidents. That requirement was met through the creation of this report. Minor tweaks to collection, cleaning, and visualizing need to be completed in order to ensure information integrity, and that process can begin when it is approved. ME office data is also easily accessed and visualized; furthermore, combining both data sets can be done in order to analyze gaps from either source.
The next step for this ask is determining if the Department wants to focus on the development of regular, programmable reports like this draft or developing an app/dashboard. Both require roughly the same amount of work. A dashboard/app will require additional permissions from the Office of the Chief Judge and the Bureau of Technology, but this is the primary barrier to this method.
A prototype of this system can be built in a matter of weeks; however, due to the public nature of this measure, the internally collected data would need to be scrubbed of any identifying information in order to protect the identity of court involved youth. The value of this prototype, beyond a proof of concept, would be the ability to analyze a key metric for the court without having to use Excel, R, or any other data analysis tool.